
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DimDivision_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_DimDivision_Insert]
GO


create procedure dbo.p_DimDivision_Insert (
	@DivisionIdentifier varchar (20),
	@DivisionName nvarchar (50),
	@ParentDivisionKey int,
	@DivisionKey int output)
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnDimDivisionInsert
        else
	    save transaction txnDimDivisionInsert

	set @Error = @@error
	if (@Error <> 0) goto cleanup

	if (not exists (select 1 
		          from dbo.DimDivision with (NOLOCK)
			  where (DivisionKey = @ParentDivisionKey)))
	begin

		set @Error = -3
		goto cleanup

	end			

	insert into dbo.DimDivision (DivisionIdentifier,
				     DivisionName,
				     ParentDivisionKey)
	  values (@DivisionIdentifier,
		  @DivisionName,
		  @ParentDivisionKey)

	select @Error = @@error,
	       @Rows = @@rowcount
	if ((@Error = 0) and
	    (@Rows = 0)) set @Error = -1
	if (@Error <> 0) goto cleanup

	set @DivisionKey = scope_identity()

cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnDimDivisionInsert
		else
		if (@inTranCount = 0) commit transaction txnDimDivisionInsert

	end

	return @Error

end
GO

grant execute on dbo.p_DimDivision_Insert  to [db_datawriter]
go
